package cn.com.dao.impl;

import cn.com.dao.CustomerDao;
import cn.com.dao.UserDao;
import cn.com.domain.Customer;
import cn.com.domain.Room;
import cn.com.domain.User;
import cn.com.utils.JDBCutils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CustomerDaoImpl implements CustomerDao {

    @Override
    public List<Customer> findAll() {
        QueryRunner runner = new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from customer";
        List<Customer> customers=null;
        try {
           customers = runner.query(sql, new BeanListHandler<Customer>(Customer.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customers;
    }

    /**
     * 添加用户
     * @param customer
     */
    @Override
    public void addCustomer(Customer customer) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="insert into customer values(?,?,?,?,?)";
        Object[] params={null,
                customer.getIdentity_num(),customer.getSex(),
                customer.getPhone(),customer.getCustomer_name()
        };
        try {
            runner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delteCustomer(Customer customer) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="delete from customer where id = ?";
        try {
            runner.update(sql,customer.getCustomer_id());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过id删除顾客信息
     * @param id
     */
    @Override
    public void delteCustomerbyid(String id) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="delete from customer where customer_id = ?";
        try {
            runner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void alterCustomerbyid(Customer customer, String id) {
        QueryRunner runner =new QueryRunner(JDBCutils.getDataSource());
        String sql="UPDATE `customer` SET `identity_num`=?, " +
                "`sex`=?, `phone`=?, " +
                "`customer_name`=? WHERE (`customer_id`=?)";
        Object[] params={customer.getIdentity_num(),customer.getSex(),
            customer.getPhone(),customer.getCustomer_name(),id};
        try {
            runner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Customer findcustomerByidentity_numandphone(String identity_num, String phone) {
        QueryRunner runner = new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from customer where identity_num = ? and phone = ?";
        Customer customer =null;
        try {
            customer  = runner.query(sql, new BeanHandler<>(Customer.class), identity_num, phone);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customer;
    }

    @Override
    public int findcustomerTotalCount() {
        String sql="select count(*) from customer";
        Connection conn=JDBCutils.getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        int i=0;
        try {
             pstmt=conn.prepareStatement(sql);
             rs = pstmt.executeQuery();
             rs.next();
            i = rs.getInt(1);
            JDBCutils.close(rs,pstmt,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }



    @Override
    public List<Customer> findcustomerByPage(int start, int rows) {
        QueryRunner runner = new QueryRunner(JDBCutils.getDataSource());
        String sql="select * from customer limit ? , ?";
        List<Customer> customers=null;
        Object[] params={start,rows};
        try {
            customers = runner.query(sql, new BeanListHandler<Customer>(Customer.class),params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customers;
    }


}
